<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@page import="java.util.*"%>
<%@page import="support.*"%>
<%@page import="java.sql.*"%>
<%
	String path = request.getContextPath();
	String basePath = request.getScheme() + "://"
			+ request.getServerName() + ":" + request.getServerPort()
			+ path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">

<title>My JSP 'register_post.jsp' starting page</title>

<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">

</head>
<body>
	<%
		//get username
		String nickName = request.getParameter("nickName");

		//get password
		String userPassword = request.getParameter("userPassword");

		//get email address
		String email = request.getParameter("userMail");

		//check with database
		Connection conn = null;
		try {
			conn = DBUtils.getConnection();
			DBUtils.beginTransaction(conn);

			boolean exist = false;
			String sql = "select * from users where user_name=?";

			PreparedStatement ps1 = conn.prepareStatement(sql);
			ps1.setString(1, nickName);

			ResultSet rs1 = ps1.executeQuery();
			if (rs1.next()) {
				exist = true;
			}
			
			String sq2 = "select * from users where email=?";

			PreparedStatement ps2 = conn.prepareStatement(sq2);
			ps2.setString(1, email);

			ResultSet rs2 = ps2.executeQuery();
			if (rs2.next()) {
				exist = true;
			}
			
			if (exist) {
				out.println("User alreasy exists.");
	%>
	<br>
	<tr>
		<p>
			<a href="register.jsp">Return to register page</a>
	</tr>
	<%
		} else {

				String encMD5Pwd = org.apache.catalina.realm.RealmBase
						.Digest(userPassword, "MD5", "utf-8");
				String sql3 = "insert into users(user_name, password, email) values(?,?,?)";
				PreparedStatement ps3 = conn.prepareStatement(sql3, Statement.RETURN_GENERATED_KEYS);
				ps3.setString(1, nickName);
				ps3.setString(2, encMD5Pwd);
				ps3.setString(3, email);
				ps3.executeUpdate();
				
				ResultSet rset3;
				rset3 = ps3.getGeneratedKeys();
				rset3.next();
				int id = rset3.getInt(1);
				
				String sql4 = "insert into user_roles(user_ref, user_name, role) values(?,?, 'student')";
				PreparedStatement ps4 = conn.prepareStatement(sql4);
				ps4.setInt(1, id);
				ps4.setString(2,nickName);
				ps4.executeUpdate();
				 
				DBUtils.commit(conn);

				out.println("Congratulations! Succesfully registered!");
	%>
	<br>
	<p>Your information:
	<p>
		Username:
		<%=nickName%>
	<p>
		Email:
		<%=email%>
		<tr>
			<p><a href="login.jsp">Apply for graduate school now!</a>
		</tr>
		<%
			}
			} catch (SQLException ex) {
				ex.printStackTrace();
				throw ex;
			} finally {
				DBUtils.closeConnection(conn);
			}
		%>
	
</body>
</html>